package ar.fiuba.fallar2.pac_man;

import java.io.FileInputStream;
import java.io.IOException;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Iterator;
import java.util.List;
import java.util.Properties;


public class DBManager {

		
	private static final String CONFIGURATION_FILE = "resources/jtds.properties";
	private static DBManager _instancia = null;
	private Connection conn;

	private DBManager() {
		init();
	}

	public static DBManager getInstance() {
		if (_instancia==null) _instancia= new DBManager();
		return _instancia;
		
	}
	
	private void init() {
	
		 try {
			    Properties Props = new Properties();
		        FileInputStream in = new FileInputStream(CONFIGURATION_FILE);
		        Props.load(in);
		        in.close();
		   
			   Class.forName(Props.getProperty("jtds.driver"));
			   String host =Props.getProperty("jtds.host");
			   String db =Props.getProperty("jtds.db");
			   String user = Props.getProperty("jtds.user");
			   String password = Props.getProperty("jtds.password");
			   String port = Props.getProperty("jtds.port");
			   String url = "jdbc:mysql://" + host + ":" + port+ "/" + db;
			
		       System.out.print(url);
		       System.out.print("\n");
		       conn = DriverManager.getConnection(url,user, password);
						
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
			throw new RuntimeException("Falta el driver jtds de sql en el classpath...", e);
		} catch (SQLException e) {
			e.printStackTrace();
			throw new RuntimeException("No se pudo conectar a la base de datos...", e);
		} catch (IOException e) {
			
			e.printStackTrace();
			throw new RuntimeException("El archivo de configuracion de la base no se encuentra en el classpath...", e);
		}
		 
	}
	public void close() throws SQLException {
		if (getConnection()!=null)	getConnection().close();
	}
	
	public Connection getConnection() {
		return conn;
	}
	
	public int save(String sql) throws SQLException {
		Statement stm = getConnection().createStatement();
		stm.executeUpdate(sql);
		ResultSet keys = stm.getGeneratedKeys();
		keys.next();
		int idGenerated = keys.getInt(1);
		keys.close();
		stm.close();
		return idGenerated;
	}
	
	public void update(String sql) throws SQLException {
		Statement stm = getConnection().createStatement();
		stm.executeUpdate(sql);
		stm.close();
	}
	
	public Statement find(String sql) throws SQLException {
		Statement stm = getConnection().createStatement();
		stm.executeQuery(sql);
		//stm.close();
		return stm;
	}
	
	
	

	public void delete (String sql) throws SQLException {
		Statement stm = getConnection().createStatement();
		stm.executeUpdate(sql);
		stm.close();
	}

	public ResultSet execStoredProcedure(String StoredName, List inputParm, List retParm)  throws SQLException
	{
         CallableStatement cs=null;
           try {
			  String sql;
			  
			  if(retParm!=null && retParm.size()>0) 
				sql="{ ? = call  "+StoredName;
			  else sql="{ call  "+StoredName;
		      
			 if( inputParm!=null && inputParm.size()>0)
			  {	 sql+=" ( ?";
				 for(int i=0;i<inputParm.size()-1;i++)	sql+=", ?";
				 sql+=" )}";
			  }
			 else sql+=" }"; 
			 
			  cs=conn.prepareCall(sql);
			  if(inputParm!=null   )
			  {
				  Iterator i=inputParm.iterator();
			  			   int contador=1;
			   while ( i.hasNext())
			   {
				Object obj=i.next();
				String type =obj.getClass().getName();
				if(type.equals("java.lang.Integer"))
				     cs.setInt(contador,(Integer)obj);
				if(type.equals("java.lang.String"))
				   cs.setString(contador, obj.toString());
				if(type.equals("java.lang.Date"))
			       	 cs.setDate(contador,(Date)obj);
				if(type.equals("java.lang.Float"))
				     cs.setFloat(contador,(Float)obj);
			     contador++;	   
			  }
			   
			  }
			  ResultSet rs = cs.executeQuery();
			  return rs;
		     
		   } 
		   catch (Exception ex)
		    {
			   ex.printStackTrace();
			}
	
		   return null;
	}
	
	
	public void test()
	   {
	
		DBManager db=new DBManager();
		try {
			db.execStoredProcedure("sp_Obtener_Tramos_Permanentes", null, null);
		      }catch (Exception ex)
		    {
			   ex.printStackTrace();
			}
	
		  
		
		
		
		/*List inputParm=new ArrayList();
	    inputParm.add(a);
	    Iterator i=inputParm.iterator();
	    System.out.print(i.next().getClass().getName());
	
		 
		
		DBManager db=new DBManager();
		String SQL = "select * from Parametros";
		Connection con = db.getConnection();
		//Statement  stm = con.createStatement();
	
		 /*  ResultSet rs = stm.executeQuery(SQL);
        while (rs.next()) {
           //System.out.println(rs.getString("au_lname") + ", " + rs.getString("au_fname"));
        } 
        rs.close();
        stm.close();

		*/
		
		System.out.print("hola");
		
	}
	public static void main(String[] args) throws SQLException
	{   
		
		
	
	
		DBManager db=new DBManager();
	//db.test();
	}
}
